package com.xky.ui.module.productManagement.select;

import com.xky.pojo.Products;
import com.xky.utils.DBUtils;

import javax.swing.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 商品服务类，处理所有与商品相关的数据库操作
 */
public class ProductService {
    
    /**
     * 查询所有商品
     * @return 商品列表
     */
    public static List<Products> selectAllProducts() {
        List<Products> productsList = new ArrayList<>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = DBUtils.getConnection();
            preparedStatement = connection.prepareStatement("SELECT * FROM products");
            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                Products product = new Products();
                product.setProductId(resultSet.getInt("product_id"));
                product.setProductCode(resultSet.getString("product_code"));
                product.setProductName(resultSet.getString("product_name"));
                product.setCategoryId(resultSet.getInt("category_id"));
                product.setUnitOfMeasure(resultSet.getString("unit_of_measure"));
                product.setMinStockLevel(resultSet.getInt("min_stock_level"));
                product.setIsActive(resultSet.getString("is_active"));
                
                // 获取创建时间和更新时间
                if (resultSet.getTimestamp("created_time") != null) {
                    product.setCreatedTime(resultSet.getTimestamp("created_time").toLocalDateTime());
                }
                if (resultSet.getTimestamp("updated_time") != null) {
                    product.setUpdatedTime(resultSet.getTimestamp("updated_time").toLocalDateTime());
                }
                
                productsList.add(product);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            JOptionPane.showMessageDialog(null, "查询失败：" + e.getMessage(), "错误", JOptionPane.ERROR_MESSAGE);
        } finally {
            DBUtils.closeAll(connection, preparedStatement, resultSet);
        }
        return productsList;
    }

    /**
     * 根据ID查询商品
     * @param id 商品ID
     * @return 商品列表
     */
    public static List<Products> selectProductById(Integer id) {
        List<Products> productsList = new ArrayList<>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = DBUtils.getConnection();
            preparedStatement = connection.prepareStatement("SELECT * FROM products WHERE product_id = ?");
            preparedStatement.setInt(1, id);
            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                Products product = new Products();
                product.setProductId(resultSet.getInt("product_id"));
                product.setProductCode(resultSet.getString("product_code"));
                product.setProductName(resultSet.getString("product_name"));
                product.setCategoryId(resultSet.getInt("category_id"));
                product.setUnitOfMeasure(resultSet.getString("unit_of_measure"));
                product.setMinStockLevel(resultSet.getInt("min_stock_level"));
                product.setIsActive(resultSet.getString("is_active"));
                
                // 获取创建时间和更新时间
                if (resultSet.getTimestamp("created_time") != null) {
                    product.setCreatedTime(resultSet.getTimestamp("created_time").toLocalDateTime());
                }
                if (resultSet.getTimestamp("updated_time") != null) {
                    product.setUpdatedTime(resultSet.getTimestamp("updated_time").toLocalDateTime());
                }
                
                productsList.add(product);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            JOptionPane.showMessageDialog(null, "查询失败：" + e.getMessage(), "错误", JOptionPane.ERROR_MESSAGE);
        } finally {
            DBUtils.closeAll(connection, preparedStatement, resultSet);
        }
        return productsList;
    }

    /**
     * 根据商品名称进行模糊查询
     * @param keyword 关键词
     * @return 商品列表
     */
    public static List<Products> searchProductsByKeyword(String keyword) {
        List<Products> productsList = new ArrayList<>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = DBUtils.getConnection();
            // 使用LIKE进行模糊查询，支持商品名称和商品编码
            preparedStatement = connection.prepareStatement(
                "SELECT * FROM products WHERE product_name LIKE ? OR product_code LIKE ?"
            );
            String searchPattern = "%" + keyword + "%";
            preparedStatement.setString(1, searchPattern);
            preparedStatement.setString(2, searchPattern);
            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                Products product = new Products();
                product.setProductId(resultSet.getInt("product_id"));
                product.setProductCode(resultSet.getString("product_code"));
                product.setProductName(resultSet.getString("product_name"));
                product.setCategoryId(resultSet.getInt("category_id"));
                product.setUnitOfMeasure(resultSet.getString("unit_of_measure"));
                product.setMinStockLevel(resultSet.getInt("min_stock_level"));
                product.setIsActive(resultSet.getString("is_active"));
                
                // 获取创建时间和更新时间
                if (resultSet.getTimestamp("created_time") != null) {
                    product.setCreatedTime(resultSet.getTimestamp("created_time").toLocalDateTime());
                }
                if (resultSet.getTimestamp("updated_time") != null) {
                    product.setUpdatedTime(resultSet.getTimestamp("updated_time").toLocalDateTime());
                }
                
                productsList.add(product);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            JOptionPane.showMessageDialog(null, "查询失败：" + e.getMessage(), "错误", JOptionPane.ERROR_MESSAGE);
        } finally {
            DBUtils.closeAll(connection, preparedStatement, resultSet);
        }
        return productsList;
    }


} 